CREATE DATABASE IF NOT EXISTS real_estate_dbb;
USE real_estate_dbb;

CREATE TABLE users(
    id INT(10) AUTO_INCREMENT PRIMARY KEY,
    login VARCHAR(20) NOT NULL,
    type VARCHAR(10) NOT NULL DEFAULT 'customer',
    pass VARCHAR(20) NOT NULL
);

-- # There are should be only 3 types of users, where only ADMIN and SELLER types need authentication:
-- # ADMIN: can add update select delete any apropriate data. His main role is to moderate data.
-- # CUSTOMER: can add update delete select only data with his/hers id. (add properties, create watchlist, search for properties)
-- # OTHERS: can only select certain appropriate data (search for properties).

CREATE TABLE properties(
    id INT(10) AUTO_INCREMENT PRIMARY KEY,
    user_id INT(10) NOT NULL,
    bedrooms INT(2) NOT NULL,
    price INT(10) NOT NULL,
    description VARCHAR(1500) NOT NULL DEFAULT 'N/A',
    date_listed TIMESTAMP DEFAULT NOW(),
    FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE addresses(
    property_id INT(10) UNIQUE PRIMARY KEY,
    city VARCHAR(40) NOT NULL,
    suburb VARCHAR(40) NOT NULL,
    street_name VARCHAR(40) NOT NULL,
    street_number INT(10) NOT NULL,
    FOREIGN KEY(property_id) REFERENCES properties(id) ON DELETE CASCADE
);

CREATE TABLE photos(
    id INT(10) AUTO_INCREMENT PRIMARY KEY,
    property_id INT(10) NOT NULL,
    photo_url VARCHAR(255) NOT NULL UNIQUE,
    FOREIGN KEY(property_id) REFERENCES properties(id) ON DELETE CASCADE
);

CREATE TABLE watchlists(
    property_id INT(10),
    user_id INT(10),
    FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY(property_id) REFERENCES properties(id) ON DELETE CASCADE,
    PRIMARY KEY(property_id, user_id)
);


INSERT INTO users( login, pass) 
VALUES ("Erum Ali", "testing");

INSERT INTO properties( user_id, bedrooms, price, description )
VALUES( 1, 4, 50000, "This stylish two storey lockwood home situated in fabulous Clarks Beach is a family dream home with plenty of space to play and grow. The upper level boasts three bedrooms one of which is the master complete with ensuite, access to the top deck and sneaky beautiful beach views. 
The family bathroom, open plan kitchen, dining and family lounge area all provide great flow through to the expansive upper-level deck that almost wraps around the entire second storey. 
Downstairs has the fourth bedroom, a good-sized rumpus and a separate laundry room, there is potential here to make this lower level into a self-contained space, which could be either extra income by renting it out or space for the teenagers, maybe even the in-laws.
Situated privately down a ROW this flat fully fenced section provides outdoor space that is easily maintained and provides plenty of room for the kids, pets and adults, this bright and sunny yard area will be well used and enjoyed.
The double internal garage is always a bonus to any home with all its uses and plenty of additional parking available too. 
Considered one of Auckland's best beaches for families, Clarks Beach abound in all its beauty is just a short distance away along with a local Yacht Club and Golf Course, not to mention glorious walks along the Harbour.");

INSERT INTO addresses (property_id, city, suburb, street_name, street_number)
VALUES (1, "Auckland", "Clarks Beach", "Clarks Beach Road", 41);

INSERT INTO photos (property_id, photo_url)
VALUES (1, "mysql/photos/property1/2021-05-13_10-00.png"),
(1, "mysql/photos/property1/2021-05-13_10-03.png");

